﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ESRI.ArcGIS.Geometry;
using System.Collections;
using System.Data.SqlClient;
using GDCal.vo;
using System.Data;

namespace GDCal.dao
{
    public class CityLandPriceDao : AbstractDao
    {
        public static ArrayList filter(CityLandPriceVO filterObj)
        {
            ArrayList ret = new ArrayList();
            SqlDataReader rs;
            CityLandPriceVO temp = null;
            SqlConnection oConn = getConnection();

            String whereClause = buildWhereClause(filterObj);
            String strSQL = "SELECT id, city, year, land_layer, land_area_attribute, map_key FROM City_Land_Price";
            if (whereClause != null)
                strSQL += whereClause;

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            while (rs.Read())
            {
                temp = new CityLandPriceVO();
                temp.setId(rs.GetInt32(0));
                temp.setCity(rs.GetString(1));
                temp.setYear(rs.GetInt32(2));
                temp.setLandLayer(rs.GetString(3));
                temp.setLandAreaAttribute(rs.GetString(4));
                temp.setMapKey(rs.GetString(5));
                ret.Add(temp);
            }

            oConn.Close();
            return ret;
        }

        private static String buildWhereClause(CityLandPriceVO filterObj)
        {
            String ret = "";
            Boolean addAnd = false;
            if (filterObj != null)
            {
                if (filterObj.getId() > 0)
                {
                    ret += " id = " + filterObj.getId();
                    addAnd = true;
                }

                if (filterObj.getCity() != null && !"".Equals(filterObj.getCity()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " city like (N'%" + filterObj.getCity() + "')";
                }

                if (filterObj.getYear() > 0)
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " year = " + filterObj.getYear();
                }

                if (filterObj.getLandLayer() != null && !"".Equals(filterObj.getLandLayer()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " land_layer like (N'%" + filterObj.getLandLayer() + "%')";
                }

                if (filterObj.getLandAreaAttribute() != null && !"".Equals(filterObj.getLandAreaAttribute()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " land_area_attribute like (N'%" + filterObj.getLandAreaAttribute() + "%')";
                }

                if (filterObj.getMapKey() != null && !"".Equals(filterObj.getMapKey()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " map_key like (N'%" + filterObj.getMapKey() + "%')";
                }

                if (!"".Equals(ret))
                {
                    ret = " WHERE" + ret;
                }
            }
            return ret;
        }

        public static int delCityLandPrice(Int32 delId)
        {
            int ret;
            SqlConnection oConn = getConnection();
            String strSQL = "DELETE FROM City_Land_Price WHERE id = " + delId;

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }

        public static CityLandPriceVO getCityLandPrice(int id)
        {
            SqlDataReader rs;
            CityLandPriceVO ret = null;
            if (id == 0)
            {
                ret = new CityLandPriceVO();
                return ret;
            }
            SqlConnection oConn = getConnection();

            String strSQL = "SELECT city, year, land_layer, land_area_attribute, map_key FROM City_Land_Price WHERE id = " + id;
            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (rs.Read())
            {
                ret = new CityLandPriceVO();
                ret.setId(id);
                ret.setCity(rs.GetString(0));
                ret.setYear(rs.GetInt32(1));
                ret.setLandLayer(rs.GetString(2));
                ret.setLandAreaAttribute(rs.GetString(3));
                ret.setMapKey(rs.GetString(4));
            }

            oConn.Close();
            return ret;
        }

        public static int insertCityLandPrice(CityLandPriceVO clpVO)
        {
            int ret;
            SqlConnection oConn = getConnection();
            String strSQL = "INSERT INTO City_Land_Price(city, year, land_layer, land_area_attribute, map_key) VALUES ("
                            + "(N'" + clpVO.getCity() + "'), "
                            + clpVO.getYear() + ", "
                            + "(N'" + clpVO.getLandLayer() + "'), "
                            + "(N'" + clpVO.getLandAreaAttribute() + "'), "
                            + "(N'" + clpVO.getMapKey() + "'))";

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }
    }
}
